-- Apagar as tabelas

DROP TABLE lpm_anos_nascimento CASCADE CONSTRAINTS 
;
DROP TABLE lpm_clans CASCADE CONSTRAINTS 
;
DROP TABLE lpm_codigos_barras CASCADE CONSTRAINTS 
;
DROP TABLE lpm_eventos CASCADE CONSTRAINTS 
;
DROP TABLE lpm_inscricoes CASCADE CONSTRAINTS 
;
DROP TABLE lpm_inscricoes_material CASCADE CONSTRAINTS 
;
DROP TABLE lpm_inscricoes_torneios CASCADE CONSTRAINTS 
;
DROP TABLE lpm_jogos CASCADE CONSTRAINTS 
;
DROP TABLE lpm_material CASCADE CONSTRAINTS 
;
DROP TABLE lpm_pagamentos CASCADE CONSTRAINTS 
;
DROP TABLE lpm_tipo_inscricao CASCADE CONSTRAINTS 
;
DROP TABLE lpm_tipo_pagamento CASCADE CONSTRAINTS 
;
DROP TABLE lpm_tipo_torneio CASCADE CONSTRAINTS 
;
DROP TABLE lpm_torneios CASCADE CONSTRAINTS 
;
DROP TABLE lpm_utilizadores CASCADE CONSTRAINTS 
;

-- Criar as tabelas e chaves primárias

CREATE TABLE lpm_anos_nascimento 
    ( 
     ano_id NUMBER (10)  NOT NULL , 
     ano_detalhe NUMBER (4)  NOT NULL 
    ) 
;



ALTER TABLE lpm_anos_nascimento 
    ADD CONSTRAINT lpm_pk_anosnascimento PRIMARY KEY ( ano_id ) ;


CREATE TABLE lpm_clans 
    ( 
     clan_id NUMBER (10)  NOT NULL , 
     clan_nome VARCHAR2 (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_clans 
    ADD CONSTRAINT lpm_pk_clans PRIMARY KEY ( clan_id ) ;


CREATE TABLE lpm_codigos_barras 
    ( 
     codbar_id NUMBER (10)  NOT NULL , 
     codbar_detalhe NUMBER (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_codigos_barras 
    ADD CONSTRAINT lpm_pk_codigosbarras PRIMARY KEY ( codbar_id ) ;



CREATE TABLE lpm_eventos 
    ( 
     ev_id NUMBER (10)  NOT NULL , 
     ev_nome VARCHAR2 (30)  NOT NULL , 
     ev_dtini DATE  NOT NULL , 
     ev_dtfim DATE  NOT NULL , 
     ev_cp1 NUMBER (4)  NOT NULL , 
     ev_cp2 NUMBER (3) , 
     ev_localidade VARCHAR2 (30) NOT NULL 
    ) 
;



ALTER TABLE lpm_eventos 
    ADD CONSTRAINT lpm_pk_eventos PRIMARY KEY ( ev_id ) ;


CREATE TABLE lpm_inscricoes 
    ( 
     insc_id NUMBER (10)  NOT NULL , 
     insc_data TIMESTAMP  NOT NULL , 
     insc_dtcheckin TIMESTAMP , 
     insc_dtcheckout TIMESTAMP , 
     insc_util_id NUMBER (10)  NOT NULL , 
     insc_pag_id NUMBER (10) , 
     insc_tinsc_id NUMBER (10)  NOT NULL , 
     insc_tpag_id NUMBER (10) , 
     insc_codbar_id NUMBER (10) , 
     insc_ev_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT lpm_pk_inscricoes PRIMARY KEY ( insc_id ) ;


CREATE TABLE lpm_inscricoes_material 
    ( 
     inscmat_mat_id NUMBER (10)  NOT NULL , 
     inscmat_insc_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_inscricoes_material 
    ADD CONSTRAINT lpm_pk_inscricoesmaterial PRIMARY KEY ( inscmat_mat_id, inscmat_insc_id ) ;


CREATE TABLE lpm_inscricoes_torneios 
    ( 
     itorn_torn_id NUMBER (10)  NOT NULL , 
     itorn_insc_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_inscricoes_torneios 
    ADD CONSTRAINT lpm_pk_inscricoestorneios PRIMARY KEY ( itorn_torn_id, itorn_insc_id ) ;


CREATE TABLE lpm_jogos 
    ( 
     jogo_id NUMBER (10)  NOT NULL , 
     jogo_nome VARCHAR2 (60)  NOT NULL , 
     jogo_foto BLOB 
    ) 
;



ALTER TABLE lpm_jogos 
    ADD CONSTRAINT lpm_pk_jogos PRIMARY KEY ( jogo_id ) ;


CREATE TABLE lpm_material 
    ( 
     mat_id NUMBER (10)  NOT NULL , 
     mat_detalhe VARCHAR2 (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_material 
    ADD CONSTRAINT lpm_pk_material PRIMARY KEY ( mat_id ) ;


CREATE TABLE lpm_pagamentos 
    ( 
     pag_id NUMBER (10)  NOT NULL , 
     pag_valor NUMBER (10)  NOT NULL , 
     pag_data DATE  NOT NULL , 
     pag_tpag_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_pagamentos 
    ADD CONSTRAINT lpm_pk_pagamentos PRIMARY KEY ( pag_id ) ;


CREATE TABLE lpm_tipo_inscricao 
    ( 
     tinsc_id NUMBER (10)  NOT NULL , 
     tinsc_detalhe VARCHAR2 (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_tipo_inscricao 
    ADD CONSTRAINT lpm_pk_tipoinscricao PRIMARY KEY ( tinsc_id ) ;


CREATE TABLE lpm_tipo_pagamento 
    ( 
     tpag_id NUMBER (10)  NOT NULL , 
     tpag_detalhe VARCHAR2 (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_tipo_pagamento 
    ADD CONSTRAINT lpm_pk_tipopagamento PRIMARY KEY ( tpag_id ) ;


CREATE TABLE lpm_tipo_torneio 
    ( 
     ttorn_id NUMBER (10)  NOT NULL , 
     ttorn_detalhe VARCHAR2 (30)  NOT NULL 
    ) 
;



ALTER TABLE lpm_tipo_torneio 
    ADD CONSTRAINT lpm_pk_tipotorneio PRIMARY KEY ( ttorn_id ) ;


CREATE TABLE lpm_torneios 
    ( 
     torn_id NUMBER (10)  NOT NULL , 
     torn_nome VARCHAR2 (60)  NOT NULL , 
     torn_num_jog NUMBER (1)  NOT NULL , 
     torn_ttorn_id NUMBER (10)  NOT NULL , 
     torn_jogo_id NUMBER (10)  NOT NULL , 
     torn_ev_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_torneios 
    ADD CONSTRAINT lpm_pk_torneios PRIMARY KEY ( torn_id ) ;


CREATE TABLE lpm_utilizadores 
    ( 
     util_id NUMBER (10)  NOT NULL , 
     util_nome VARCHAR2 (60)  NOT NULL , 
     util_nick VARCHAR2 (30)  NOT NULL , 
     util_bi NUMBER (10)  NOT NULL , 
     util_cp1 NUMBER (4)  NOT NULL , 
     util_cp2 NUMBER (3) , 
     util_localidade VARCHAR2 (30)  NOT NULL , 
     util_foto BLOB , 
     util_clan_id NUMBER (10) , 
     util_ano_id NUMBER (10)  NOT NULL 
    ) 
;



ALTER TABLE lpm_utilizadores 
    ADD CONSTRAINT lpm_pk_utilizadores PRIMARY KEY ( util_id ) ;


-- Criar as chaves estrangeiras


ALTER TABLE lpm_inscricoes_material 
    ADD CONSTRAINT inscmat_fk_inscricoes FOREIGN KEY 
    ( 
     inscmat_insc_id
    ) 
    REFERENCES lpm_inscricoes 
    ( 
     insc_id
    ) 
;


ALTER TABLE lpm_inscricoes_material 
    ADD CONSTRAINT inscmat_fk_material FOREIGN KEY 
    ( 
     inscmat_mat_id
    ) 
    REFERENCES lpm_material 
    ( 
     mat_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_codbar FOREIGN KEY 
    ( 
     insc_codbar_id
    ) 
    REFERENCES lpm_codigos_barras 
    ( 
     codbar_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_eventos FOREIGN KEY 
    ( 
     insc_ev_id
    ) 
    REFERENCES lpm_eventos 
    ( 
     ev_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_pagamentos FOREIGN KEY 
    ( 
     insc_pag_id
    ) 
    REFERENCES lpm_pagamentos 
    ( 
     pag_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_tipoinscricao FOREIGN KEY 
    ( 
     insc_tinsc_id
    ) 
    REFERENCES lpm_tipo_inscricao 
    ( 
     tinsc_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_tipopagamento FOREIGN KEY 
    ( 
     insc_tpag_id
    ) 
    REFERENCES lpm_tipo_pagamento 
    ( 
     tpag_id
    ) 
;


ALTER TABLE lpm_inscricoes 
    ADD CONSTRAINT inscricoes_fk_utilizadores FOREIGN KEY 
    ( 
     insc_util_id
    ) 
    REFERENCES lpm_utilizadores 
    ( 
     util_id
    ) 
;


ALTER TABLE lpm_inscricoes_torneios 
    ADD CONSTRAINT insctorn_fk_torneios FOREIGN KEY 
    ( 
     itorn_torn_id
    ) 
    REFERENCES lpm_torneios 
    ( 
     torn_id
    ) 
;


ALTER TABLE lpm_inscricoes_torneios 
    ADD CONSTRAINT insctorn_fk_inscricoes FOREIGN KEY 
    ( 
     itorn_insc_id
    ) 
    REFERENCES lpm_inscricoes 
    ( 
     insc_id
    ) 
;


ALTER TABLE lpm_pagamentos 
    ADD CONSTRAINT pagamentos_fk_tipopagamento FOREIGN KEY 
    ( 
     pag_tpag_id
    ) 
    REFERENCES lpm_tipo_pagamento 
    ( 
     tpag_id
    ) 
;


ALTER TABLE lpm_torneios 
    ADD CONSTRAINT torneios_fk_eventos FOREIGN KEY 
    ( 
     torn_ev_id
    ) 
    REFERENCES lpm_eventos 
    ( 
     ev_id
    ) 
;


ALTER TABLE lpm_torneios 
    ADD CONSTRAINT torneios_fk_jogos FOREIGN KEY 
    ( 
     torn_jogo_id
    ) 
    REFERENCES lpm_jogos 
    ( 
     jogo_id
    ) 
;


ALTER TABLE lpm_torneios 
    ADD CONSTRAINT torneios_fk_tipotorneio FOREIGN KEY 
    ( 
     torn_ttorn_id
    ) 
    REFERENCES lpm_tipo_torneio 
    ( 
     ttorn_id
    ) 
;


ALTER TABLE lpm_utilizadores 
    ADD CONSTRAINT utilizadores_fk_anosnascimento FOREIGN KEY 
    ( 
     util_ano_id
    ) 
    REFERENCES lpm_anos_nascimento 
    ( 
     ano_id
    ) 
;


ALTER TABLE lpm_utilizadores 
    ADD CONSTRAINT utilizadores_fk_clans FOREIGN KEY 
    ( 
     util_clan_id
    ) 
    REFERENCES lpm_clans 
    ( 
     clan_id
    ) 
;